﻿/******************************************************************************/
/***               Generated by IBExpert 29.12.2015 10:28:09                ***/
/******************************************************************************/

/******************************************************************************/
/***      Following SET SQL DIALECT is just for the Database Comparer       ***/
/******************************************************************************/
SET SQL DIALECT 3;



/******************************************************************************/
/***                                 Tables                                 ***/
/******************************************************************************/


CREATE GENERATOR STORZDTGDS_ID;
CREATE GENERATOR OBJ_CLASS_ID;

CREATE TABLE STORZDTGDS (
    ID                  INTEGER NOT NULL,
    SZID                INTEGER NOT NULL,
    GODSID              INTEGER NOT NULL,
    OBJID               INTEGER,
    SOURCE              DOUBLE PRECISION DEFAULT 0.0 NOT NULL,
    RSQUANT             DOUBLE PRECISION DEFAULT 0.0 NOT NULL,
    BQUANT              DOUBLE PRECISION DEFAULT 0.0,
    SRC_ONE             DOUBLE PRECISION DEFAULT 0.0 NOT NULL,
    SQNT_ONE            DOUBLE PRECISION DEFAULT 0.0 NOT NULL,
    PRICE               DOUBLE PRECISION DEFAULT 0.0 NOT NULL,
    SIZEX               DOUBLE PRECISION DEFAULT 0.0 NOT NULL,
    SIZEY               DOUBLE PRECISION DEFAULT 0.0 NOT NULL,
    SIZEZ               DOUBLE PRECISION DEFAULT 0.0 NOT NULL,
    DESCRIPTION         BLOB SUB_TYPE 0 SEGMENT SIZE 80,
    PRICEID             INTEGER,
    SNPRICE             DOUBLE PRECISION DEFAULT 0,
    DISCOUNTBONUSSETID  INTEGER
);




/******************************************************************************/
/***                              Primary Keys                              ***/
/******************************************************************************/

ALTER TABLE STORZDTGDS ADD CONSTRAINT STORZDTGDS_PK PRIMARY KEY (ID);


/******************************************************************************/
/***                              Foreign Keys                              ***/
/******************************************************************************/

ALTER TABLE STORZDTGDS ADD CONSTRAINT STORZDTGDS_DISCOUNTBONUSSETID FOREIGN KEY (DISCOUNTBONUSSETID) REFERENCES DK_DISCOUNTBONUSSETS (ID);
ALTER TABLE STORZDTGDS ADD CONSTRAINT STORZDTGDS_GODSID FOREIGN KEY (GODSID) REFERENCES GOODS (ID) ON UPDATE CASCADE;
ALTER TABLE STORZDTGDS ADD CONSTRAINT STORZDTGDS_OBJID FOREIGN KEY (OBJID) REFERENCES OBJ_CLASS (ID) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE STORZDTGDS ADD CONSTRAINT STORZDTGDS_PRICEID FOREIGN KEY (PRICEID) REFERENCES PRICELIST (ID) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE STORZDTGDS ADD CONSTRAINT STORZDTGDS_SZID FOREIGN KEY (SZID) REFERENCES STORZAKAZDT (ID) ON DELETE CASCADE ON UPDATE CASCADE;


/******************************************************************************/
/***                                Triggers                                ***/
/******************************************************************************/


SET TERM ^ ;



/******************************************************************************/
/***                          Triggers for tables                           ***/
/******************************************************************************/



/* Trigger: STORZDTGDS_AD0 */
CREATE OR ALTER TRIGGER STORZDTGDS_AD0 FOR STORZDTGDS
ACTIVE AFTER DELETE POSITION 0
AS
begin
  if (Old.Source <> 0.0 and Old.Price <> 0.0) then
    Update StorZakazDt set Summa = Summa - (Old.Source * Old.Price)
    where ID = Old.SZID;
end
^


/* Trigger: STORZDTGDS_AI0 */
CREATE OR ALTER TRIGGER STORZDTGDS_AI0 FOR STORZDTGDS
ACTIVE AFTER INSERT POSITION 0
AS
begin
  if (New.Source <> 0.0 and New.Price <> 0.0) then
    Update StorZakazDt set Summa = Summa + (New.Source * New.Price)
    where ID = New.SZID;
end
^


/* Trigger: STORZDTGDS_AU0 */
CREATE OR ALTER TRIGGER STORZDTGDS_AU0 FOR STORZDTGDS
ACTIVE AFTER UPDATE POSITION 0
AS
Declare Variable NewSumma double precision;
Declare Variable OldSumma double precision;
declare variable OldState integer;
declare variable State3 integer;
begin
  NewSumma = New.Source * New.Price;
  OldSumma = Old.Source * Old.Price;
  if (:NewSumma <>  :OldSumma) then
    Update StorZakazDt set Summa = Summa - :OldSumma + :NewSumma
    where ID = New.SZID;
  if ((New.Source <> Old.Source) or (New.RSQuant <> Old.RSQuant) ) then begin
    Select State from StorZakazDt where ID = New.SZID into :OldState;
    State3 = 0;
    if (not exists (Select D.ID from StorZDtGds D where D.SZID = New.SZID and
        Abs(D.Source - D.RSQuant) > 1.0e-5)) then State3 = 1;
    if (:OldState < 3 and :State3 = 1) then
      Update StorZakazDt set State = 3, ReadyFDateTime = Current_Date where ID = New.SZID;
    if (:OldState >= 3 and :State3 = 0) then
      Update StorZakazDt Set State = 0, ReadyFDateTime = Null where ID = New.SZID;
  end
end
^


/* Trigger: STORZDTGDS_BD0 */
CREATE OR ALTER TRIGGER STORZDTGDS_BD0 FOR STORZDTGDS
ACTIVE BEFORE DELETE POSITION 0
AS
begin
  Delete from Obj_Class where ID = Old.ObjID;
end
^


/* Trigger: STORZDTGDS_BI0 */
CREATE OR ALTER TRIGGER STORZDTGDS_BI0 FOR STORZDTGDS
ACTIVE BEFORE INSERT POSITION 0
AS
declare variable ObjID integer;
begin
  if (New.ID is Null) then New.ID = Gen_ID(StorZDtGds_ID, 1);
  ObjID = Gen_ID(Obj_Class_ID, 1);
  insert Into Obj_Class(ID, Obj_Typ) Values(:ObjID, 201);
  New.ObjID = :ObjID;
end
^


/* Trigger: STORZDTGDS_BU0 */
CREATE OR ALTER TRIGGER STORZDTGDS_BU0 FOR STORZDTGDS
ACTIVE BEFORE UPDATE POSITION 0
AS
begin
  if (Exists (Select D.ID from GddDt D where D.SzGdsID = New.ID) ) then begin
    New.SizeX = Old.SizeX;
    New.SizeY = Old.SizeY;
    New.SizeZ = Old.SizeZ;
  end
  if (New.Source <> Old.Source) then begin
    if (New.Source < New.RSQuant) then
      New.Source = New.RSQuant;
  end
end
^


SET TERM ; ^



/******************************************************************************/
/***                              Descriptions                              ***/
/******************************************************************************/

COMMENT ON TABLE STORZDTGDS IS 
'Товар в заказах на отгрузку товара';



/******************************************************************************/
/***                          Fields descriptions                           ***/
/******************************************************************************/

COMMENT ON COLUMN STORZDTGDS.SZID IS 
'ID заказа';

COMMENT ON COLUMN STORZDTGDS.GODSID IS 
'ID товара';

COMMENT ON COLUMN STORZDTGDS.SOURCE IS 
'Кол-во товара в заказе';

COMMENT ON COLUMN STORZDTGDS.RSQUANT IS 
'зарезервировано товара';

COMMENT ON COLUMN STORZDTGDS.BQUANT IS 
'Баланс (при принудительном закрытии заказа)
SOURCE-RSQUANT';

COMMENT ON COLUMN STORZDTGDS.SRC_ONE IS 
'Кол-во (шт.)';

COMMENT ON COLUMN STORZDTGDS.SQNT_ONE IS 
'Зарезервировано товара (в шт.)';

COMMENT ON COLUMN STORZDTGDS.PRICE IS 
'Отпускная цена (с НДС)';

COMMENT ON COLUMN STORZDTGDS.DESCRIPTION IS 
'Описание товара';

COMMENT ON COLUMN STORZDTGDS.PRICEID IS 
'Прайс-лист
(при выборе цен по прайс-листу)';

COMMENT ON COLUMN STORZDTGDS.SNPRICE IS 
'Исходная цена товара (без скидки)';

COMMENT ON COLUMN STORZDTGDS.DISCOUNTBONUSSETID IS 
'Идентификатор набора скидок и бонусов';



/******************************************************************************/
/***                               Privileges                               ***/
/******************************************************************************/
